#!/usr/bin/env python3
"""
数据库优化和备份模块
Database Optimization and Backup Module
"""

import sqlite3
import os
import shutil
import json
import gzip
import threading
import time
from datetime import datetime, timedelta
from typing import List, Dict, Any, Optional
import schedule
import hashlib
from dataclasses import dataclass, asdict
import logging
from pathlib import Path

@dataclass
class BackupInfo:
    """备份信息"""
    backup_id: str
    file_path: str
    file_size: int
    checksum: str
    created_at: datetime
    backup_type: str  # full, incremental, differential
    description: str
    is_compressed: bool

@dataclass
class DatabaseStats:
    """数据库统计信息"""
    total_size: int
    table_count: int
    total_records: int
    largest_table: str
    oldest_record: datetime
    newest_record: datetime
    fragmentation_ratio: float
    index_usage: Dict[str, Any]

class DatabaseOptimizer:
    """数据库优化器"""
    
    def __init__(self, db_path: str):
        self.db_path = db_path
        self.logger = self._setup_logger()
        
    def _setup_logger(self):
        """设置日志"""
        logger = logging.getLogger('DatabaseOptimizer')
        logger.setLevel(logging.INFO)
        
        if not logger.handlers:
            handler = logging.FileHandler('database_optimization.log')
            formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
            handler.setFormatter(formatter)
            logger.addHandler(handler)
        
        return logger
    
    def analyze_database(self) -> DatabaseStats:
        """分析数据库状态"""
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            # 获取文件大小
            file_size = os.path.getsize(self.db_path)
            
            # 获取表列表
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            tables = [row[0] for row in cursor.fetchall()]
            table_count = len(tables)
            
            # 统计总记录数和找出最大表
            total_records = 0
            largest_table = ""
            max_records = 0
            
            table_stats = {}
            for table in tables:
                cursor.execute(f"SELECT COUNT(*) FROM {table};")
                count = cursor.fetchone()[0]
                total_records += count
                table_stats[table] = count
                
                if count > max_records:
                    max_records = count
                    largest_table = table
            
            # 获取时间范围（基于sensor_data表）
            oldest_record = None
            newest_record = None
            
            if 'sensor_data' in tables:
                cursor.execute("SELECT MIN(timestamp), MAX(timestamp) FROM sensor_data;")
                result = cursor.fetchone()
                if result[0] and result[1]:
                    oldest_record = datetime.fromisoformat(result[0])
                    newest_record = datetime.fromisoformat(result[1])
            
            # 计算碎片化率
            cursor.execute("PRAGMA freelist_count;")
            free_pages = cursor.fetchone()[0]
            cursor.execute("PRAGMA page_count;")
            total_pages = cursor.fetchone()[0]
            
            fragmentation_ratio = (free_pages / total_pages) * 100 if total_pages > 0 else 0
            
            # 分析索引使用情况
            index_usage = self._analyze_index_usage(cursor)
            
            conn.close()
            
            stats = DatabaseStats(
                total_size=file_size,
                table_count=table_count,
                total_records=total_records,
                largest_table=largest_table,
                oldest_record=oldest_record or datetime.now(),
                newest_record=newest_record or datetime.now(),
                fragmentation_ratio=fragmentation_ratio,
                index_usage=index_usage
            )
            
            self.logger.info(f"数据库分析完成: {file_size} bytes, {table_count} tables, {total_records} records")
            return stats
            
        except Exception as e:
            self.logger.error(f"数据库分析失败: {e}")
            raise
    
    def _analyze_index_usage(self, cursor) -> Dict[str, Any]:
        """分析索引使用情况"""
        try:
            # 获取所有索引
            cursor.execute("SELECT name, tbl_name FROM sqlite_master WHERE type='index';")
            indexes = cursor.fetchall()
            
            index_info = {}
            for index_name, table_name in indexes:
                if not index_name.startswith('sqlite_'):  # 跳过系统索引
                    # 获取索引信息
                    cursor.execute(f"PRAGMA index_info({index_name});")
                    columns = [row[2] for row in cursor.fetchall()]
                    
                    index_info[index_name] = {
                        'table': table_name,
                        'columns': columns,
                        'created': True
                    }
            
            return index_info
            
        except Exception as e:
            self.logger.warning(f"索引分析失败: {e}")
            return {}
    
    def optimize_database(self) -> Dict[str, Any]:
        """优化数据库"""
        optimization_results = {
            'vacuum_executed': False,
            'indexes_created': 0,
            'old_data_cleaned': 0,
            'size_before': 0,
            'size_after': 0,
            'optimization_time': 0
        }
        
        start_time = time.time()
        
        try:
            # 记录优化前大小
            optimization_results['size_before'] = os.path.getsize(self.db_path)
            
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            # 1. 执行VACUUM清理碎片
            self.logger.info("开始执行VACUUM操作...")
            cursor.execute("VACUUM;")
            optimization_results['vacuum_executed'] = True
            self.logger.info("VACUUM操作完成")
            
            # 2. 重建统计信息
            cursor.execute("ANALYZE;")
            
            # 3. 创建缺失的索引
            missing_indexes = self._create_missing_indexes(cursor)
            optimization_results['indexes_created'] = missing_indexes
            
            # 4. 清理旧数据
            cleaned_records = self._cleanup_old_data(cursor)
            optimization_results['old_data_cleaned'] = cleaned_records
            
            conn.commit()
            conn.close()
            
            # 记录优化后大小
            optimization_results['size_after'] = os.path.getsize(self.db_path)
            optimization_results['optimization_time'] = time.time() - start_time
            
            self.logger.info(f"数据库优化完成，用时 {optimization_results['optimization_time']:.2f} 秒")
            
            return optimization_results
            
        except Exception as e:
            self.logger.error(f"数据库优化失败: {e}")
            raise
    
    def _create_missing_indexes(self, cursor) -> int:
        """创建缺失的索引"""
        indexes_created = 0
        
        # 定义需要的索引
        required_indexes = [
            ("idx_sensor_timestamp", "sensor_data", ["timestamp"]),
            ("idx_irrigation_timestamp", "irrigation_log", ["timestamp"]),
            ("idx_pest_timestamp", "pest_detection", ["timestamp"]),
            ("idx_device_timestamp", "device_status", ["timestamp"]),
            ("idx_weather_timestamp", "weather_forecast", ["timestamp"]),
            ("idx_fault_device", "fault_events", ["device_id", "timestamp"]),
            ("idx_maintenance_device", "maintenance_tasks", ["device_id", "scheduled_date"]),
            ("idx_user_sessions_user", "user_sessions", ["user_id"]),
            ("idx_user_logs_user", "user_logs", ["user_id", "timestamp"]),
        ]
        
        for index_name, table_name, columns in required_indexes:
            try:
                # 检查表是否存在
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?;", (table_name,))
                if not cursor.fetchone():
                    continue
                
                # 检查索引是否已存在
                cursor.execute("SELECT name FROM sqlite_master WHERE type='index' AND name=?;", (index_name,))
                if cursor.fetchone():
                    continue
                
                # 创建索引
                columns_str = ", ".join(columns)
                sql = f"CREATE INDEX IF NOT EXISTS {index_name} ON {table_name} ({columns_str});"
                cursor.execute(sql)
                indexes_created += 1
                self.logger.info(f"创建索引: {index_name} on {table_name}")
                
            except Exception as e:
                self.logger.warning(f"创建索引 {index_name} 失败: {e}")
        
        return indexes_created
    
    def _cleanup_old_data(self, cursor, retention_days: int = 90) -> int:
        """清理旧数据"""
        cleaned_records = 0
        cutoff_date = (datetime.now() - timedelta(days=retention_days)).isoformat()
        
        # 定义需要清理的表
        cleanup_tables = [
            ("sensor_data", "timestamp"),
            ("irrigation_log", "timestamp"),
            ("pest_detection", "timestamp"),
            ("device_status", "timestamp"),
            ("weather_forecast", "timestamp"),
            ("user_logs", "timestamp"),
        ]
        
        for table_name, timestamp_column in cleanup_tables:
            try:
                # 检查表是否存在
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?;", (table_name,))
                if not cursor.fetchone():
                    continue
                
                # 统计要删除的记录数
                cursor.execute(f"SELECT COUNT(*) FROM {table_name} WHERE {timestamp_column} < ?;", (cutoff_date,))
                count = cursor.fetchone()[0]
                
                if count > 0:
                    # 删除旧记录
                    cursor.execute(f"DELETE FROM {table_name} WHERE {timestamp_column} < ?;", (cutoff_date,))
                    cleaned_records += count
                    self.logger.info(f"从 {table_name} 清理了 {count} 条旧记录")
                
            except Exception as e:
                self.logger.warning(f"清理表 {table_name} 失败: {e}")
        
        return cleaned_records
    
    def check_database_health(self) -> Dict[str, Any]:
        """检查数据库健康状况"""
        health_report = {
            'integrity_check': False,
            'corruption_detected': False,
            'performance_issues': [],
            'recommendations': []
        }
        
        try:
            conn = sqlite3.connect(self.db_path)
            cursor = conn.cursor()
            
            # 执行完整性检查
            cursor.execute("PRAGMA integrity_check;")
            integrity_result = cursor.fetchone()[0]
            health_report['integrity_check'] = integrity_result == "ok"
            
            if integrity_result != "ok":
                health_report['corruption_detected'] = True
                health_report['recommendations'].append("数据库存在完整性问题，建议立即备份并修复")
            
            # 检查性能问题
            stats = self.analyze_database()
            
            if stats.fragmentation_ratio > 10:
                health_report['performance_issues'].append("数据库碎片化严重")
                health_report['recommendations'].append("执行VACUUM操作清理碎片")
            
            if stats.total_size > 100 * 1024 * 1024:  # 100MB
                health_report['performance_issues'].append("数据库文件过大")
                health_report['recommendations'].append("考虑清理旧数据或分区存储")
            
            # 检查缺失的索引
            missing_indexes = self._check_missing_indexes(cursor)
            if missing_indexes:
                health_report['performance_issues'].append("缺少重要索引")
                health_report['recommendations'].append(f"创建缺失的索引: {', '.join(missing_indexes)}")
            
            conn.close()
            
            self.logger.info("数据库健康检查完成")
            return health_report
            
        except Exception as e:
            self.logger.error(f"数据库健康检查失败: {e}")
            health_report['corruption_detected'] = True
            health_report['recommendations'].append(f"健康检查失败: {str(e)}")
            return health_report
    
    def _check_missing_indexes(self, cursor) -> List[str]:
        """检查缺失的索引"""
        missing = []
        
        required_indexes = [
            "idx_sensor_timestamp",
            "idx_irrigation_timestamp",
            "idx_pest_timestamp",
            "idx_device_timestamp"
        ]
        
        for index_name in required_indexes:
            cursor.execute("SELECT name FROM sqlite_master WHERE type='index' AND name=?;", (index_name,))
            if not cursor.fetchone():
                missing.append(index_name)
        
        return missing

class DatabaseBackupManager:
    """数据库备份管理器"""
    
    def __init__(self, db_path: str, backup_dir: str = "backups"):
        self.db_path = db_path
        self.backup_dir = backup_dir
        self.backup_history = []
        self.logger = self._setup_logger()
        
        # 确保备份目录存在
        Path(backup_dir).mkdir(parents=True, exist_ok=True)
        
        # 加载备份历史
        self._load_backup_history()
        
        # 启动定期备份
        self._schedule_automatic_backups()
    
    def _setup_logger(self):
        """设置日志"""
        logger = logging.getLogger('DatabaseBackup')
        logger.setLevel(logging.INFO)
        
        if not logger.handlers:
            handler = logging.FileHandler('database_backup.log')
            formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
            handler.setFormatter(formatter)
            logger.addHandler(handler)
        
        return logger
    
    def _load_backup_history(self):
        """加载备份历史"""
        history_file = os.path.join(self.backup_dir, "backup_history.json")
        if os.path.exists(history_file):
            try:
                with open(history_file, 'r', encoding='utf-8') as f:
                    data = json.load(f)
                    self.backup_history = [
                        BackupInfo(
                            backup_id=item['backup_id'],
                            file_path=item['file_path'],
                            file_size=item['file_size'],
                            checksum=item['checksum'],
                            created_at=datetime.fromisoformat(item['created_at']),
                            backup_type=item['backup_type'],
                            description=item['description'],
                            is_compressed=item.get('is_compressed', False)
                        ) for item in data
                    ]
            except Exception as e:
                self.logger.warning(f"加载备份历史失败: {e}")
                self.backup_history = []
    
    def _save_backup_history(self):
        """保存备份历史"""
        history_file = os.path.join(self.backup_dir, "backup_history.json")
        try:
            data = []
            for backup in self.backup_history:
                item = asdict(backup)
                item['created_at'] = backup.created_at.isoformat()
                data.append(item)
            
            with open(history_file, 'w', encoding='utf-8') as f:
                json.dump(data, f, ensure_ascii=False, indent=2)
        except Exception as e:
            self.logger.error(f"保存备份历史失败: {e}")
    
    def _calculate_checksum(self, file_path: str) -> str:
        """计算文件校验和"""
        hash_md5 = hashlib.md5()
        with open(file_path, "rb") as f:
            for chunk in iter(lambda: f.read(4096), b""):
                hash_md5.update(chunk)
        return hash_md5.hexdigest()
    
    def create_backup(self, backup_type: str = "full", description: str = "", 
                     compress: bool = True) -> Optional[BackupInfo]:
        """创建备份"""
        try:
            # 生成备份文件名
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            backup_id = f"{backup_type}_{timestamp}"
            
            if compress:
                backup_filename = f"{backup_id}.db.gz"
            else:
                backup_filename = f"{backup_id}.db"
            
            backup_path = os.path.join(self.backup_dir, backup_filename)
            
            self.logger.info(f"开始创建备份: {backup_path}")
            
            # 创建备份
            if compress:
                with open(self.db_path, 'rb') as f_in:
                    with gzip.open(backup_path, 'wb') as f_out:
                        shutil.copyfileobj(f_in, f_out)
            else:
                shutil.copy2(self.db_path, backup_path)
            
            # 计算文件大小和校验和
            file_size = os.path.getsize(backup_path)
            
            # 对于压缩文件，计算原文件的校验和
            if compress:
                checksum = self._calculate_checksum(self.db_path)
            else:
                checksum = self._calculate_checksum(backup_path)
            
            # 创建备份信息
            backup_info = BackupInfo(
                backup_id=backup_id,
                file_path=backup_path,
                file_size=file_size,
                checksum=checksum,
                created_at=datetime.now(),
                backup_type=backup_type,
                description=description,
                is_compressed=compress
            )
            
            # 添加到历史记录
            self.backup_history.append(backup_info)
            self._save_backup_history()
            
            # 清理旧备份
            self._cleanup_old_backups()
            
            self.logger.info(f"备份创建成功: {backup_path} ({file_size} bytes)")
            return backup_info
            
        except Exception as e:
            self.logger.error(f"创建备份失败: {e}")
            return None
    
    def restore_backup(self, backup_id: str, target_path: str = None) -> bool:
        """恢复备份"""
        try:
            # 查找备份
            backup_info = None
            for backup in self.backup_history:
                if backup.backup_id == backup_id:
                    backup_info = backup
                    break
            
            if not backup_info:
                self.logger.error(f"未找到备份: {backup_id}")
                return False
            
            if not os.path.exists(backup_info.file_path):
                self.logger.error(f"备份文件不存在: {backup_info.file_path}")
                return False
            
            target = target_path or self.db_path
            
            self.logger.info(f"开始恢复备份: {backup_info.file_path} -> {target}")
            
            # 恢复备份
            if backup_info.is_compressed:
                with gzip.open(backup_info.file_path, 'rb') as f_in:
                    with open(target, 'wb') as f_out:
                        shutil.copyfileobj(f_in, f_out)
            else:
                shutil.copy2(backup_info.file_path, target)
            
            # 验证恢复的文件
            restored_checksum = self._calculate_checksum(target)
            if restored_checksum != backup_info.checksum:
                self.logger.error("恢复的文件校验和不匹配")
                return False
            
            self.logger.info(f"备份恢复成功: {target}")
            return True
            
        except Exception as e:
            self.logger.error(f"恢复备份失败: {e}")
            return False
    
    def _cleanup_old_backups(self, max_backups: int = 10):
        """清理旧备份"""
        if len(self.backup_history) <= max_backups:
            return
        
        # 按创建时间排序，删除最旧的备份
        self.backup_history.sort(key=lambda x: x.created_at, reverse=True)
        
        backups_to_remove = self.backup_history[max_backups:]
        self.backup_history = self.backup_history[:max_backups]
        
        for backup in backups_to_remove:
            try:
                if os.path.exists(backup.file_path):
                    os.remove(backup.file_path)
                    self.logger.info(f"删除旧备份: {backup.file_path}")
            except Exception as e:
                self.logger.warning(f"删除备份文件失败: {e}")
        
        self._save_backup_history()
    
    def _schedule_automatic_backups(self):
        """安排自动备份"""
        # 每天凌晨2点执行全量备份
        schedule.every().day.at("02:00").do(
            lambda: self.create_backup("full", "自动全量备份")
        )
        
        # 每6小时执行增量备份
        schedule.every(6).hours.do(
            lambda: self.create_backup("incremental", "自动增量备份")
        )
        
        # 启动调度器线程
        def run_scheduler():
            while True:
                schedule.run_pending()
                time.sleep(60)  # 每分钟检查一次
        
        scheduler_thread = threading.Thread(target=run_scheduler, daemon=True)
        scheduler_thread.start()
        
        self.logger.info("自动备份调度器已启动")
    
    def list_backups(self) -> List[BackupInfo]:
        """列出所有备份"""
        return sorted(self.backup_history, key=lambda x: x.created_at, reverse=True)
    
    def delete_backup(self, backup_id: str) -> bool:
        """删除备份"""
        try:
            backup_info = None
            for i, backup in enumerate(self.backup_history):
                if backup.backup_id == backup_id:
                    backup_info = backup
                    del self.backup_history[i]
                    break
            
            if not backup_info:
                return False
            
            if os.path.exists(backup_info.file_path):
                os.remove(backup_info.file_path)
            
            self._save_backup_history()
            self.logger.info(f"删除备份: {backup_id}")
            return True
            
        except Exception as e:
            self.logger.error(f"删除备份失败: {e}")
            return False
    
    def verify_backup(self, backup_id: str) -> bool:
        """验证备份完整性"""
        try:
            backup_info = None
            for backup in self.backup_history:
                if backup.backup_id == backup_id:
                    backup_info = backup
                    break
            
            if not backup_info or not os.path.exists(backup_info.file_path):
                return False
            
            # 如果是压缩文件，需要解压后验证
            if backup_info.is_compressed:
                import tempfile
                with tempfile.NamedTemporaryFile() as temp_file:
                    with gzip.open(backup_info.file_path, 'rb') as f_in:
                        shutil.copyfileobj(f_in, temp_file)
                    
                    temp_file.seek(0)
                    checksum = self._calculate_checksum(temp_file.name)
            else:
                checksum = self._calculate_checksum(backup_info.file_path)
            
            return checksum == backup_info.checksum
            
        except Exception as e:
            self.logger.error(f"验证备份失败: {e}")
            return False

class DatabaseManager:
    """数据库管理器（整合优化和备份）"""
    
    def __init__(self, db_path: str, backup_dir: str = "backups"):
        self.db_path = db_path
        self.optimizer = DatabaseOptimizer(db_path)
        self.backup_manager = DatabaseBackupManager(db_path, backup_dir)
        self.logger = self._setup_logger()
    
    def _setup_logger(self):
        """设置日志"""
        logger = logging.getLogger('DatabaseManager')
        logger.setLevel(logging.INFO)
        
        if not logger.handlers:
            handler = logging.FileHandler('database_manager.log')
            formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
            handler.setFormatter(formatter)
            logger.addHandler(handler)
        
        return logger
    
    def daily_maintenance(self):
        """执行日常维护"""
        self.logger.info("开始执行日常数据库维护")
        
        try:
            # 1. 健康检查
            health_report = self.optimizer.check_database_health()
            self.logger.info(f"健康检查完成: {health_report}")
            
            # 2. 创建备份
            backup_info = self.backup_manager.create_backup("daily", "日常维护备份")
            if backup_info:
                self.logger.info(f"日常备份创建成功: {backup_info.backup_id}")
            
            # 3. 如果需要，执行优化
            if health_report['performance_issues']:
                self.logger.info("检测到性能问题，开始优化...")
                optimization_results = self.optimizer.optimize_database()
                self.logger.info(f"优化完成: {optimization_results}")
            
            self.logger.info("日常维护完成")
            
        except Exception as e:
            self.logger.error(f"日常维护失败: {e}")
    
    def emergency_backup(self, description: str = "紧急备份"):
        """创建紧急备份"""
        return self.backup_manager.create_backup("emergency", description, compress=False)
    
    def get_system_status(self) -> Dict[str, Any]:
        """获取系统状态"""
        try:
            stats = self.optimizer.analyze_database()
            health = self.optimizer.check_database_health()
            backups = self.backup_manager.list_backups()
            
            return {
                'database_stats': asdict(stats),
                'health_report': health,
                'backup_count': len(backups),
                'latest_backup': backups[0].created_at.isoformat() if backups else None,
                'total_backup_size': sum(b.file_size for b in backups)
            }
            
        except Exception as e:
            self.logger.error(f"获取系统状态失败: {e}")
            return {'error': str(e)}

def create_database_management_gui():
    """创建数据库管理GUI"""
    import tkinter as tk
    from tkinter import ttk, messagebox
    
    class DatabaseManagementGUI:
        def __init__(self, db_path: str = "enhanced_agriculture.db"):
            self.root = tk.Tk()
            self.root.title("数据库管理")
            self.root.geometry("800x600")
            
            self.db_manager = DatabaseManager(db_path)
            
            self.setup_ui()
            self.refresh_status()
        
        def setup_ui(self):
            # 创建笔记本组件
            notebook = ttk.Notebook(self.root)
            notebook.pack(fill='both', expand=True, padx=10, pady=10)
            
            # 状态监控标签页
            status_frame = ttk.Frame(notebook)
            notebook.add(status_frame, text='📊 状态监控')
            self.create_status_tab(status_frame)
            
            # 备份管理标签页
            backup_frame = ttk.Frame(notebook)
            notebook.add(backup_frame, text='💾 备份管理')
            self.create_backup_tab(backup_frame)
            
            # 优化维护标签页
            optimize_frame = ttk.Frame(notebook)
            notebook.add(optimize_frame, text='⚡ 优化维护')
            self.create_optimize_tab(optimize_frame)
        
        def create_status_tab(self, parent):
            # 状态信息显示
            self.status_text = tk.Text(parent, height=25, bg='#f8f8ff')
            scrollbar = ttk.Scrollbar(parent, orient='vertical', command=self.status_text.yview)
            self.status_text.configure(yscrollcommand=scrollbar.set)
            
            self.status_text.pack(side='left', fill='both', expand=True)
            scrollbar.pack(side='right', fill='y')
            
            # 刷新按钮
            refresh_btn = ttk.Button(parent, text="🔄 刷新状态", command=self.refresh_status)
            refresh_btn.pack(side='bottom', pady=5)
        
        def create_backup_tab(self, parent):
            # 备份控制
            control_frame = ttk.LabelFrame(parent, text="备份控制")
            control_frame.pack(fill='x', padx=5, pady=5)
            
            ttk.Button(control_frame, text="创建全量备份", 
                      command=lambda: self.create_backup("full")).pack(side='left', padx=5, pady=5)
            ttk.Button(control_frame, text="创建增量备份", 
                      command=lambda: self.create_backup("incremental")).pack(side='left', padx=5, pady=5)
            ttk.Button(control_frame, text="紧急备份", 
                      command=lambda: self.create_backup("emergency")).pack(side='left', padx=5, pady=5)
            
            # 备份列表
            list_frame = ttk.LabelFrame(parent, text="备份列表")
            list_frame.pack(fill='both', expand=True, padx=5, pady=5)
            
            columns = ('ID', '类型', '大小', '创建时间')
            self.backup_tree = ttk.Treeview(list_frame, columns=columns, show='headings')
            
            for col in columns:
                self.backup_tree.heading(col, text=col)
                self.backup_tree.column(col, width=150)
            
            backup_scrollbar = ttk.Scrollbar(list_frame, orient='vertical', command=self.backup_tree.yview)
            self.backup_tree.configure(yscrollcommand=backup_scrollbar.set)
            
            self.backup_tree.pack(side='left', fill='both', expand=True)
            backup_scrollbar.pack(side='right', fill='y')
            
            # 备份操作按钮
            btn_frame = ttk.Frame(list_frame)
            btn_frame.pack(side='bottom', fill='x', pady=5)
            
            ttk.Button(btn_frame, text="恢复备份", command=self.restore_backup).pack(side='left', padx=5)
            ttk.Button(btn_frame, text="验证备份", command=self.verify_backup).pack(side='left', padx=5)
            ttk.Button(btn_frame, text="删除备份", command=self.delete_backup).pack(side='left', padx=5)
        
        def create_optimize_tab(self, parent):
            # 优化控制
            control_frame = ttk.LabelFrame(parent, text="数据库优化")
            control_frame.pack(fill='x', padx=5, pady=5)
            
            ttk.Button(control_frame, text="健康检查", command=self.health_check).pack(side='left', padx=5, pady=5)
            ttk.Button(control_frame, text="优化数据库", command=self.optimize_database).pack(side='left', padx=5, pady=5)
            ttk.Button(control_frame, text="日常维护", command=self.daily_maintenance).pack(side='left', padx=5, pady=5)
            
            # 结果显示
            result_frame = ttk.LabelFrame(parent, text="操作结果")
            result_frame.pack(fill='both', expand=True, padx=5, pady=5)
            
            self.result_text = tk.Text(result_frame, height=20, bg='#f8f8ff')
            result_scrollbar = ttk.Scrollbar(result_frame, orient='vertical', command=self.result_text.yview)
            self.result_text.configure(yscrollcommand=result_scrollbar.set)
            
            self.result_text.pack(side='left', fill='both', expand=True)
            result_scrollbar.pack(side='right', fill='y')
        
        def refresh_status(self):
            try:
                status = self.db_manager.get_system_status()
                
                self.status_text.delete('1.0', tk.END)
                self.status_text.insert('end', "📊 数据库系统状态\n")
                self.status_text.insert('end', "=" * 50 + "\n\n")
                
                if 'error' in status:
                    self.status_text.insert('end', f"❌ 错误: {status['error']}\n")
                    return
                
                # 数据库统计
                stats = status['database_stats']
                self.status_text.insert('end', "📈 数据库统计:\n")
                self.status_text.insert('end', f"  文件大小: {stats['total_size'] / 1024 / 1024:.2f} MB\n")
                self.status_text.insert('end', f"  表数量: {stats['table_count']}\n")
                self.status_text.insert('end', f"  总记录数: {stats['total_records']}\n")
                self.status_text.insert('end', f"  最大表: {stats['largest_table']}\n")
                self.status_text.insert('end', f"  碎片化率: {stats['fragmentation_ratio']:.2f}%\n\n")
                
                # 健康报告
                health = status['health_report']
                self.status_text.insert('end', "🏥 健康状况:\n")
                self.status_text.insert('end', f"  完整性检查: {'✅ 通过' if health['integrity_check'] else '❌ 失败'}\n")
                self.status_text.insert('end', f"  数据损坏: {'❌ 是' if health['corruption_detected'] else '✅ 否'}\n")
                
                if health['performance_issues']:
                    self.status_text.insert('end', f"  性能问题: {', '.join(health['performance_issues'])}\n")
                else:
                    self.status_text.insert('end', "  性能问题: ✅ 无\n")
                
                # 备份信息
                self.status_text.insert('end', "\n💾 备份信息:\n")
                self.status_text.insert('end', f"  备份数量: {status['backup_count']}\n")
                if status['latest_backup']:
                    self.status_text.insert('end', f"  最新备份: {status['latest_backup']}\n")
                self.status_text.insert('end', f"  备份总大小: {status['total_backup_size'] / 1024 / 1024:.2f} MB\n")
                
                # 更新备份列表
                self.refresh_backup_list()
                
            except Exception as e:
                messagebox.showerror("错误", f"刷新状态失败: {e}")
        
        def refresh_backup_list(self):
            # 清空现有项目
            for item in self.backup_tree.get_children():
                self.backup_tree.delete(item)
            
            # 添加备份项目
            backups = self.db_manager.backup_manager.list_backups()
            for backup in backups:
                self.backup_tree.insert('', 'end', values=(
                    backup.backup_id,
                    backup.backup_type,
                    f"{backup.file_size / 1024:.1f} KB",
                    backup.created_at.strftime('%Y-%m-%d %H:%M:%S')
                ))
        
        def create_backup(self, backup_type):
            try:
                description = f"手动{backup_type}备份"
                backup_info = self.db_manager.backup_manager.create_backup(backup_type, description)
                if backup_info:
                    messagebox.showinfo("成功", f"备份创建成功: {backup_info.backup_id}")
                    self.refresh_backup_list()
                else:
                    messagebox.showerror("错误", "备份创建失败")
            except Exception as e:
                messagebox.showerror("错误", f"创建备份失败: {e}")
        
        def restore_backup(self):
            selection = self.backup_tree.selection()
            if not selection:
                messagebox.showwarning("警告", "请选择要恢复的备份")
                return
            
            backup_id = self.backup_tree.item(selection[0])['values'][0]
            
            if messagebox.askyesno("确认", f"确定要恢复备份 {backup_id} 吗？\n这将覆盖当前数据库！"):
                try:
                    if self.db_manager.backup_manager.restore_backup(backup_id):
                        messagebox.showinfo("成功", "备份恢复成功")
                    else:
                        messagebox.showerror("错误", "备份恢复失败")
                except Exception as e:
                    messagebox.showerror("错误", f"恢复备份失败: {e}")
        
        def verify_backup(self):
            selection = self.backup_tree.selection()
            if not selection:
                messagebox.showwarning("警告", "请选择要验证的备份")
                return
            
            backup_id = self.backup_tree.item(selection[0])['values'][0]
            
            try:
                if self.db_manager.backup_manager.verify_backup(backup_id):
                    messagebox.showinfo("验证结果", "备份验证成功，文件完整")
                else:
                    messagebox.showerror("验证结果", "备份验证失败，文件可能已损坏")
            except Exception as e:
                messagebox.showerror("错误", f"验证备份失败: {e}")
        
        def delete_backup(self):
            selection = self.backup_tree.selection()
            if not selection:
                messagebox.showwarning("警告", "请选择要删除的备份")
                return
            
            backup_id = self.backup_tree.item(selection[0])['values'][0]
            
            if messagebox.askyesno("确认", f"确定要删除备份 {backup_id} 吗？"):
                try:
                    if self.db_manager.backup_manager.delete_backup(backup_id):
                        messagebox.showinfo("成功", "备份删除成功")
                        self.refresh_backup_list()
                    else:
                        messagebox.showerror("错误", "备份删除失败")
                except Exception as e:
                    messagebox.showerror("错误", f"删除备份失败: {e}")
        
        def health_check(self):
            try:
                health_report = self.db_manager.optimizer.check_database_health()
                
                self.result_text.delete('1.0', tk.END)
                self.result_text.insert('end', "🏥 数据库健康检查结果\n")
                self.result_text.insert('end', "=" * 40 + "\n\n")
                
                self.result_text.insert('end', f"完整性检查: {'✅ 通过' if health_report['integrity_check'] else '❌ 失败'}\n")
                self.result_text.insert('end', f"数据损坏: {'❌ 检测到' if health_report['corruption_detected'] else '✅ 未检测到'}\n\n")
                
                if health_report['performance_issues']:
                    self.result_text.insert('end', "⚠️ 性能问题:\n")
                    for issue in health_report['performance_issues']:
                        self.result_text.insert('end', f"  - {issue}\n")
                    self.result_text.insert('end', "\n")
                
                if health_report['recommendations']:
                    self.result_text.insert('end', "💡 建议:\n")
                    for rec in health_report['recommendations']:
                        self.result_text.insert('end', f"  - {rec}\n")
                
            except Exception as e:
                messagebox.showerror("错误", f"健康检查失败: {e}")
        
        def optimize_database(self):
            if messagebox.askyesno("确认", "数据库优化可能需要较长时间，确定继续吗？"):
                try:
                    results = self.db_manager.optimizer.optimize_database()
                    
                    self.result_text.delete('1.0', tk.END)
                    self.result_text.insert('end', "⚡ 数据库优化结果\n")
                    self.result_text.insert('end', "=" * 40 + "\n\n")
                    
                    self.result_text.insert('end', f"VACUUM执行: {'✅ 是' if results['vacuum_executed'] else '❌ 否'}\n")
                    self.result_text.insert('end', f"创建索引: {results['indexes_created']} 个\n")
                    self.result_text.insert('end', f"清理记录: {results['old_data_cleaned']} 条\n")
                    self.result_text.insert('end', f"优化前大小: {results['size_before'] / 1024 / 1024:.2f} MB\n")
                    self.result_text.insert('end', f"优化后大小: {results['size_after'] / 1024 / 1024:.2f} MB\n")
                    
                    size_saved = results['size_before'] - results['size_after']
                    self.result_text.insert('end', f"节省空间: {size_saved / 1024 / 1024:.2f} MB\n")
                    self.result_text.insert('end', f"优化时间: {results['optimization_time']:.2f} 秒\n")
                    
                    messagebox.showinfo("完成", "数据库优化完成")
                    self.refresh_status()
                    
                except Exception as e:
                    messagebox.showerror("错误", f"数据库优化失败: {e}")
        
        def daily_maintenance(self):
            if messagebox.askyesno("确认", "执行日常维护（包括备份和优化），确定继续吗？"):
                try:
                    self.db_manager.daily_maintenance()
                    
                    self.result_text.delete('1.0', tk.END)
                    self.result_text.insert('end', "🔧 日常维护完成\n")
                    self.result_text.insert('end', "=" * 40 + "\n\n")
                    self.result_text.insert('end', "✅ 健康检查完成\n")
                    self.result_text.insert('end', "✅ 备份创建完成\n")
                    self.result_text.insert('end', "✅ 数据库优化完成\n")
                    
                    messagebox.showinfo("完成", "日常维护完成")
                    self.refresh_status()
                    
                except Exception as e:
                    messagebox.showerror("错误", f"日常维护失败: {e}")
        
        def run(self):
            self.root.mainloop()
    
    return DatabaseManagementGUI()

if __name__ == "__main__":
    # 测试数据库管理功能
    gui = create_database_management_gui()
    gui.run()